﻿using KZ.Express.ENT;
using KZ.Express.Resource;
using KZ.Utilities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;

namespace KZ.Express.DAL
{
    public interface IDataAccessBase
    {
        void BeginTransaction();

        void CommitTransaction();

        void RollbackTransaction();

        int Insert(EntityBase entity);

        int Update(EntityBase entity);

        void Delete<T>(string content);

        EntityBase SelectByID<T>(int entityID);

        EntityBase SelectEntity<T>(SearchCriteria search);

        DataTable SelectDataTable<T>(SearchCriteria search);
    }

    public class DataAccessBase : IDataAccessBase
    {
        protected static DataAccessHelper _helper;

        protected EntityBase ConvertFromDataRow<T>(PropertyInfo[] properties, DataRow row)
        {
            EntityBase entity = Activator.CreateInstance(typeof(T)) as EntityBase;
            foreach (PropertyInfo property in properties)
            {
                if (row.Table.Columns.Contains(property.Name) && row[property.Name] != DBNull.Value)
                {
                    property.SetValue(entity, row[property.Name]);
                }
            }

            return entity;
        }

        public DataAccessBase()
        {
            if (_helper == null)
            {
                _helper = new DataAccessHelper();
            }
        }

        public void BeginTransaction()
        {
            _helper.BeginTransaction();
        }

        public void CommitTransaction()
        {
            _helper.CommitTransaction();
        }

        public void RollbackTransaction()
        {
            _helper.RollbackTransaction();
        }

        /// <summary>
        /// Insert into database
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Insert(EntityBase entity)
        {
            //string tableName = entity.Table;
            //if (entity is SupplierENT)
            //    tableName = "Suppliers";
            //else if (entity is CustomerENT)
            //    tableName = "Customers";
            //else if (entity is OriginalPriceListENT)
            //    tableName = "OriginalPriceLists";
            //else if (entity is CustomerPriceListENT)
            //    tableName = "CustomerPriceLists";

            //List<SqlParameter> tempSql = new List<SqlParameter>();
            //tempSql.Add(new SqlParameter("IsContinue", 0));
            //tempSql.Add(new SqlParameter("Table", tableName));
            //tempSql[0].Direction = ParameterDirection.Output;

            //int isContinue = _helper.ExecuteNonQuery("proc_Demo_Check", tempSql.ToArray());
            //if (isContinue != 1)
            //{
            //    throw new DataAccessException("đây là phiên bản demo, bạn không thể lưu thêm được nữa");
            //}
            //else
            //{
            Type type = entity.GetType();
            PropertyInfo[] properties = type.GetProperties();

            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo property in properties)
            {
                Attribute attributes = property.GetCustomAttribute(typeof(NotInsertUpdateAttribute), true);
                if (attributes == null)
                {
                    parameters.Add(new SqlParameter(property.Name, property.GetValue(entity, null)));

                    attributes = property.GetCustomAttribute(typeof(OutputAttribute), true);
                    if (attributes != null)
                        parameters[parameters.Count - 1].Direction = ParameterDirection.InputOutput;
                }
            }

            return _helper.ExecuteNonQuery(entity.InsertProcedure, parameters.ToArray());
            //}
        }

        /// <summary>
        /// Insert into database
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Update(EntityBase entity)
        {
            Type type = entity.GetType();
            PropertyInfo[] properties = type.GetProperties();

            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (PropertyInfo property in properties)
            {
                Attribute attributes = property.GetCustomAttribute(typeof(NotInsertUpdateAttribute), true);
                if (attributes == null)
                {
                    parameters.Add(new SqlParameter(property.Name, property.GetValue(entity, null)));

                    attributes = property.GetCustomAttribute(typeof(OutputAttribute), true);
                    if (attributes != null)
                        parameters[parameters.Count - 1].Direction = ParameterDirection.InputOutput;
                }
            }

            return _helper.ExecuteNonQuery(entity.UpdateProcedure, parameters.ToArray());
        }

        /// <summary>
        /// Delete from database
        /// </summary>
        /// <param name="content"></param>
        public void Delete<T>(string content)
        {
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();
            EntityBase entity = Activator.CreateInstance(type) as EntityBase;

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter("UserID", UserENT.USERID));
            parameters.Add(new SqlParameter("Table", entity.Table));
            parameters.Add(new SqlParameter("PrimaryColumn", properties.First().Name));
            parameters.Add(new SqlParameter("Content", content));

            _helper.ExecuteNonQuery(entity.DeleteProcedure, parameters.ToArray());
        }

        /// <summary>
        /// Get by id, only one record got from database
        /// </summary>
        /// <param name="entityID"></param>
        /// <returns></returns>
        public EntityBase SelectByID<T>(int entityID)
        {
            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();

            List<SqlParameter> parameters = new List<SqlParameter>();
            parameters.Add(new SqlParameter(properties.First().Name, entityID));

            EntityBase entity = Activator.CreateInstance(type) as EntityBase;
            DataTable dt = _helper.ExecuteQuery(entity.SelectProcedure, parameters.ToArray());

            if (dt.Rows.Count > 0)
                return ConvertFromDataRow<T>(properties, dt.Rows[0]);
            return entity;
        }

        /// <summary>
        /// Get by id, only one record got from database
        /// </summary>
        /// <param name="entityID"></param>
        /// <returns></returns>
        public EntityBase SelectEntity<T>(SearchCriteria search)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (KeyValuePair<string, object> item in search)
                parameters.Add(new SqlParameter(item.Key, item.Value));

            Type type = typeof(T);
            PropertyInfo[] properties = type.GetProperties();

            EntityBase entity = Activator.CreateInstance(type) as EntityBase;
            DataTable dt = _helper.ExecuteQuery(entity.SelectProcedure, parameters.ToArray());

            if (dt.Rows.Count > 0)
                return ConvertFromDataRow<T>(properties, dt.Rows[0]);
            return entity;
        }

        /// <summary>
        /// Get list, many record got from database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public DataTable SelectDataTable<T>(SearchCriteria search)
        {
            List<SqlParameter> parameters = new List<SqlParameter>();
            foreach (KeyValuePair<string, object> item in search)
                parameters.Add(new SqlParameter(item.Key, item.Value));

            Type type = typeof(T);
            EntityBase entity = Activator.CreateInstance(type) as EntityBase;
            return _helper.ExecuteQuery(entity.SelectProcedure, parameters.ToArray());
        }
    }
}